from base import session, User

from sqlalchemy.orm.exc import MultipleResultsFound, NoResultFound
split = "\n####################\n"



query = session.query(User).filter(User.name.like('%ed')).order_by(User.id)
print(query.all())
print(query.first())

try:
    print(query.one())
except MultipleResultsFound:
    print("Multiple rows were found for one_or_none()")
#one_or_none() is like one(), except that if no results are found, it doesn’t raise an error; it just returns None.
#Like one(), however, it does raise an error if multiple results are found.

print(split)
try:
    user = query.filter(User.id == 99).one()
except NoResultFound:
    print("No row was found for one()")

print(split)
query = session.query(User.id).filter(User.name == 'ed').order_by(User.id)
print(query.scalar())

print(split)

#Using Textual SQL
from sqlalchemy import text
for user in session.query(User).filter(text("id<224")).order_by(text("id")).all():
    print(user.name)

print(split)
print(session.query(User).filter(text("id<:value and name=:name")).\
    params(value=224, name='fred').order_by(User.id).one())

print(split)
print(session.query(User).from_statement(
                    text("SELECT * FROM users where name=:name")).\
                    params(name='ed').all())


stmt = text("SELECT name, id, fullname, password FROM users where name=:name")
stmt = stmt.columns(User.name, User.id, User.fullname, User.password)
print(session.query(User).from_statement(stmt).params(name='ed').all())
print(split)
print(session.query(User).filter(User.name.like('%ed')).count())
print(split)
from sqlalchemy import func #absolutely cannot tolerate the subquery
print(session.query(func.count(User.name), User.name).group_by(User.name).all())

#To achieve our simple SELECT count(*) FROM table, we can apply it as:
print(split)
print(session.query(func.count('*')).select_from(User).scalar())
